/**
 * Sql Machine
 * @version 林继双
 * @author  Sange
 */
package zozopo.database;



import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import java.util.Map.Entry;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import zozopo.operations.comm.OperatingService;


public class SqlMachine extends OperatingService{
	
	
	private static final Log logger = LogFactory.getLog(SqlMachine.class);

	/**
	 * @param DBConnection dataBaseConnection 一个数据库连接
	 * @author 林继双
	 * @return SqlMachine                     返回sql生成器
	 * @see SqlMachine(DBConnection dataBaseConnection) 初始化
	 */
	public SqlMachine(Connection conn){
		this.databaseConnection = new BusiDatabaseManager();
		
		this.conn = conn; 
		
	}
	
	/**
	 * @param String id,String tableName        表字段(数值类型),表名
	 * @author 林继双
	 * @return int                               返回当前最大的id
	 * @see getMaxId(String id,String tableName)  获取前最大的id
	 */
	public int getMaxId(String id,String tableName){
		int maxId = 0;
		String sql = "select max("+id+") as maxId from "+tableName+"";
		
		ResultSet result = this.databaseConnection.executeQuery(conn, sql);
		try {
			if(result.next()){
				maxId = result.getInt("maxId");
			}else{
				maxId = -1;
				throw new SQLException(); 
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return maxId;
	}
	
	/**
	 * @param String id,String tableName        
	 * @author 林继双
	 */
	public int countField(String field,String tableName){
		int count = 0;
		String sql = "select count("+field+") as count from "+tableName+"";
		
		ResultSet result = this.databaseConnection.executeQuery(conn, sql);
		try {
			if(result.next()){
				count = result.getInt("count");
			}else{
				count = -1;
				throw new SQLException(); 
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}
	
	/**
	 * @param String id,String tableName        
	 * @author 林继双
	 */
	public int countField(String field,String wFiled,Object wObjectVau,String tableName){
		int countNum = 0;
		
		String value = toDataBaseStyle(wObjectVau);
		String sql = "select count("+field+") as countNum from "+tableName+" where " + wFiled + "="+value;
		
		ResultSet result = this.databaseConnection.executeQuery(conn, sql);
		try {
			if(result.next()){
				countNum = result.getInt("countNum");
			}else{
				countNum = -1;
				throw new SQLException(); 
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return countNum;
	}
	
	/**
	 * @param String id,String tableName        
	 * @author 林继双
	 */
	public int sumField(String field,String wFiled,Object wObjectVau,String tableName){
		int sumNum = 0;
		
		String value = toDataBaseStyle(wObjectVau);
		String sql = "select sum("+field+") as sumNum from "+tableName+" where " + wFiled + "="+value;
		
		ResultSet result = this.databaseConnection.executeQuery(conn, sql);
	
		try {
			if(result.next()){
				sumNum = result.getInt("sumNum");
			}else{
				sumNum = -1;
				throw new SQLException(); 
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sumNum;
	}
	
	/**
	 * @param String id,String tableName        
	 * @author 林继双
	 */
	public double avgFieldForDouble(String field,String wFiled,Object wObjectVau,String tableName){
		double avgNum = 0;
		
		String value = toDataBaseStyle(wObjectVau);
		String sql = "select avg("+field+") as avgNum from "+tableName+" where " + wFiled + "="+value;
		
		ResultSet result = this.databaseConnection.executeQuery(conn, sql);
		try {
			if(result.next()){
				avgNum = result.getDouble("avgNum");
			}else{
				avgNum = -1;
				throw new SQLException(); 
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return avgNum;
	}
	
	/**
	 * @param String id,String tableName        表字段(数值类型),表名
	 * @author 林继双
	 * @return int                               返回当前最大的id
	 * @see getMaxId(String id,String tableName,String wField , Object wObjectVau)  获取前最大的id
	 */
	public int getMaxId(String id,String tableName,String wField , Object wObjectVau){
		
		int maxId = 0;
		String wFalue = toDataBaseStyle(wObjectVau);
		String sql = "select max("+id+") as maxId from "+tableName+" where " + wField + "="+wFalue;
		
		ResultSet result = this.databaseConnection.executeQuery(conn, sql);
		try {
			if(result.next()){
				maxId = result.getInt("maxId");
			}else{
				maxId = -1;
				throw new SQLException(); 
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return maxId;
	}
	
	/**
	 * @param String tableName , HashMap<String, String> insertMap       表名,<表字段,插入值>
	 * @author 林继双
	 * @return int                               插入成功数 
	 * @see insertRow( String tableName , HashMap<String, String> insertMap) 批量插入
	 */
	public boolean insertRow( String tableName , HashMap<String, Object> insertMap){
		String sql = "insert into " + tableName +" (";
		StringBuffer fields = new StringBuffer();
		StringBuffer values = new StringBuffer();
		int i =0;
		Set<Entry<String, Object>> set = insertMap.entrySet();
		Iterator<Entry<String, Object>> iterator = set.iterator();
		while(iterator.hasNext()){
			String field = "";
			String value = "";
			Object objectVau;
			
			Entry<String, Object> entry= iterator.next();
			field = entry.getKey();
			
			objectVau = entry.getValue();
			if(objectVau != null){
				value = toDataBaseStyle(objectVau);
			}
			
			
			if(i>0){
				fields.append(","+field);
				values.append(","+value);
			}else{
				fields.append(field);
				values.append(value);
			}
			i++;
		}
		sql = sql+fields.toString()+") "+"values("+values.toString()+")";
		
		if(logger.isDebugEnabled()){
			logger.debug("insertRow:"+sql.toString());
		}
		System.out.println(sql);
		boolean result = this.databaseConnection.executeUpdate(conn,sql);
		return result;
	}
	
	/**
	 * @param String tableName , HashMap<String, String> insertMap       表名,<表字段,插入值>
	 * @author 林继双
	 * @return int                               插入成功数 
	 * @see updateRow( String tableName , HashMap<String, Object> updateMap) 
	 */
	public boolean updateRow( String tableName , HashMap<String, Object> updateMap,String wField,Object wValue){
		wValue = toDataBaseStyle(wValue);
	
		StringBuffer sql = new StringBuffer();
		sql.append("update ");
		sql.append(tableName);
		sql.append(" set ");
		
		boolean isfirst = true;
		Set<Entry<String, Object>> set = updateMap.entrySet();
		Iterator<Entry<String, Object>> iterator = set.iterator();
		while(iterator.hasNext()){
			String value = "";
			Object objectVau;
			
			Entry<String, Object> entry = iterator.next();
			objectVau = entry.getValue();
			
			value = toDataBaseStyle(objectVau);
			
			if(isfirst){
				sql.append(entry.getKey());
				sql.append("="+value);
				isfirst = false;
			}else{
				sql.append(","+entry.getKey());
				sql.append("="+value);
			}
			
		}
		sql.append(" where ");
		sql.append(wField);
		sql.append("="+wValue);
		
		if(logger.isDebugEnabled()){
			logger.debug("updateRow:"+sql.toString());
		}
		System.out.println(sql.toString());
		boolean result = this.databaseConnection.executeUpdate(conn,sql.toString());
		return result;
	}
	
	/**
	 * @param String tableName , String wField , String wValue  表名,表字段,操作值
	 * @author 林继双
	 * @return int                               成功数 
	 * @see deleteRow(String tableName , String wField , String wValue ) 单个删除行
	 */
	public boolean deleteRow(String tableName , String wField , Object wObjectVau ){
		String value = "";
		
		if(wObjectVau instanceof String){
			value = "'"+wObjectVau.toString()+"'";
		}else{
			value = wObjectVau.toString();
		}
		String sql = "delete from "+tableName+" where "+wField+"="+value+"";
		
		if(logger.isDebugEnabled()){
			logger.debug("deleteRow:"+sql.toString());
		}
		//System.out.println(sql.toString());
		return this.databaseConnection.executeUpdate(conn,sql);
	}
	
	/**
	 * @param String tableName , String wField , String wValue  表名,表字段,操作值
	 * @author 林继双
	 * @return int                               成功数 
	 * @see deleteRow(String tableName , String wField , String wValue ) 单个删除行
	 */
	public boolean deleteAllRow(String tableName){
		String sql = "delete from "+tableName;
		
		if(logger.isDebugEnabled()){
			logger.debug("deleteRow:"+sql.toString());
		}
		//System.out.println(sql.toString());
		return this.databaseConnection.executeUpdate(conn,sql);
	}
	
	
	/**
	 * @param String tableName , String wField,String wValue ,String field,String fieldVale  表名,操作的表字段,操作表的值,操作表字段,操作表值
	 * @author 林继双
	 * @return int                               成功数 
	 * @see updateRow(String tableName , String wField,String wValue ,String field,String fieldVale) 单个更新行
	 */
	public boolean updateRow(String tableName , String wField,Object wValue ,String field,Object fieldVale){
		String sql = "update "+tableName+" set "+field+"="+toDataBaseStyle(fieldVale)+" where "+wField+"="+toDataBaseStyle(wValue)+"";
		System.out.println(sql);
		return this.databaseConnection.executeUpdate(conn,sql);
	}
	
	/**
	 * @param String tableName  表名
	 * @author 林继双
	 * @return ResultSet 成功数据
	 * @see 能过表名查询所有数据
	 */
	public ResultSet selectAllRecord(String tableName){
		String sql = "select * from " + tableName;
		
		if(logger.isDebugEnabled()){
			logger.debug("selectAllRecord:"+sql.toString());
		}
	
		return databaseConnection.executeQuery(conn,sql);
	}
	
	/**
	 * @param String tableName  表名
	 * @author 林继双
	 * @return ResultSet 成功数据
	 * @see 能过表名查询所有数据
	 */
	public ResultSet selectAllRecord(String tableName,String oderbyFIeld,int beginPageNum,int pageSize){
		String sql = "select * from " + tableName +" order by "+oderbyFIeld+" desc limit "+beginPageNum+","+pageSize; ;
		
		if(logger.isDebugEnabled()){
			logger.debug("selectAllRecord:"+sql.toString());
		}
	
		return databaseConnection.executeQuery(conn,sql);
	}
	
	/**
	 * @param String tableName , String distinctField 表名 ,去除重复的字段
	 * @author 林继双
	 * @return ResultSet 成功数据
	 * @see distinctRecord(String tableName,String distinctField) 去除重复记录
	 */
	public ResultSet distinctRecord(String tableName,String distinctField){
		String sql = "select distinct("+distinctField+") from " + tableName;
		if(logger.isDebugEnabled()){
			logger.debug("distinctRecord:"+sql.toString());
		}
		System.out.println("distinctRecord:"+sql); 
		return databaseConnection.executeQuery(conn,sql);
	}
	
	/**
	 * @param String tableName , String wField , String wValue  表名,表字段,操作值
	 * @author 林继双
	 * @return int                               成功数 
	 
	 */
	
	public ResultSet QueryRow(String tableName , String wField , String wValue ){
		String sql = "select * from "+tableName+" where "+wField+"='"+wValue+"'";
		System.out.println(sql);
		
		if(logger.isDebugEnabled()){
			logger.debug("QueryRow:"+sql.toString());
		}
		return databaseConnection.executeQuery(conn,sql);
	}
	
	/**
	 * @param String tableName , String wField , String wValue  表名,表字段,操作值
	 * @author 林继双
	 * @return int                               成功数 
	 
	 */
	public ResultSet QueryRow(String tableName , String wField , List<?> wValueList,int beginPageNum,int pageSize){
		StringBuffer sqlbuff = new StringBuffer();
		StringBuffer caseBuff = new StringBuffer();
		sqlbuff.append("select * from ");
		sqlbuff.append(tableName);
		sqlbuff.append(" WHERE ");
		sqlbuff.append(wField);
		sqlbuff.append(" IN ");
		sqlbuff.append("(");
		caseBuff.append("( CASE");
		int i = 1;
		for(Object wValue:wValueList){
			//sqlbuff.append("'");
			caseBuff.append(" WHEN ");
			caseBuff.append(wField);
			caseBuff.append(" = ");
			caseBuff.append(toDataBaseStyle(wValue));
			
			sqlbuff.append(toDataBaseStyle(wValue));
			if(!(i == wValueList.size())){
				sqlbuff.append(",");
				
			}
			caseBuff.append(" THEN ");
			caseBuff.append((i-1));
			i++;
		}
		caseBuff.append(" END) ");
		
		sqlbuff.append(" ) ");
		sqlbuff.append(" ORDER BY ");
		sqlbuff.append(caseBuff.toString());
		sqlbuff.append(" limit ");
		sqlbuff.append(beginPageNum);
		sqlbuff.append(",");
		sqlbuff.append(pageSize);
		
		//"+ReleaseTB.RELEASE_DATE+" desc limit "+beginPageNum+","+pageSize; 
		
		
		System.out.println(sqlbuff.toString());
		
		if(logger.isDebugEnabled()){
			logger.debug("QueryRow:"+sqlbuff.toString());
		}
		return databaseConnection.executeQuery(conn,sqlbuff.toString());
	}
	
	/**
	 * @param String tableName , String wField , String wValue  表名,表字段,操作值
	 * @author 林继双
	 * @return int                               成功数 
	
	 */
	public ResultSet QueryRow(String tableName , String wField , List<?> wValueList){
		StringBuffer sqlbuff = new StringBuffer();
		sqlbuff.append("select * from ");
		sqlbuff.append(tableName);
		sqlbuff.append(" where ");
		int i = 1;
		for(Object wValue:wValueList){
			sqlbuff.append(wField);
			sqlbuff.append("=");
			sqlbuff.append(toDataBaseStyle(wValue));
			if(!(i == wValueList.size())){
				sqlbuff.append(" or ");
			}
			i++;
		}
		
		
		//System.out.println(sqlbuff.toString());
		
		if(logger.isDebugEnabled()){
			logger.debug("QueryRow:"+sqlbuff.toString());
		}
		return databaseConnection.executeQuery(conn,sqlbuff.toString());
	}
	
	/**
	 * @param String tableName , String wField , String wValue  表名,表字段,操作值
	 * @author 林继双
	 * @return int                               成功数 
	 * @see 
	 */
	public static String toDataBaseStyle(Object ObjectVau){
		if(ObjectVau == null){
			return "''";
		}
		String value = "";
		if(ObjectVau instanceof String){
			ObjectVau = ObjectVau.toString().replace("'", "''");     //'  解决方法
			value = "'"+ObjectVau.toString()+"'";
		}else{
			value = ObjectVau.toString();
		}
		return value;
	}
	
	public static String toInStrs(String[] args){
		
		StringBuffer strbuff = new StringBuffer();
		
		boolean isFirst = true;
		strbuff.append("(");
		
		for(String str:args){
			if(isFirst){
				strbuff.append("'"+str+"'");
				isFirst = false;
			}else{
				strbuff.append(",'"+str+"'");
			}
			
		}
		strbuff.append(")");
		
		return strbuff.toString();
		
	}
	
	public static boolean isContainsChinase(String str){
		
		int n =0;
		for(int i=0; i<str.length(); i++) {
			n = (int)str.charAt(i);
			if((19968 <= n && n <40623)) {
				return true;
			}
		}
		
		return false;
		
	}
}
